﻿using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using PSI.Helper;
using PSI.Models.TMS;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace PSI.Services.TMS {
    public class InPlanService : PSIBaseService {
        private List<InPlan> GetInPlanListWithFlag(NameValueCollection formData) {
            var planNumber = formData["planNumber"];
            var address = formData["address"];
            var cntNumber = formData["cntNumber"];
            var dispatcher = formData["dispatcher"];
            var truckCompany = formData["truckCompany"];
            var truckNumber = formData["truckNumber"];
            var driver = formData["driver"];
            var driverTel = formData["driverTel"];
            var planDT1 = formData["planDT1"];
            var planDT2 = formData["planDT2"];
            var status = formData["status"];
            var plandt = formData["time"];
            var consignationBillNumber = formData["consignationBillNumber"];

           
            var colList = new List<string> { 
                "b.id", "b.f_plan_num", "b.f_cnt_num", "a.f_arrival_dt", "b.f_address",
                "b.f_cnt_type", "b.f_plan_dt", "b.f_dispatcher",
                "b.f_truck_company", "b.f_truck_num", "b.f_driver", "b.f_driver_tel",
                "b.f_truck_dt", "b.f_dt_1_plan", "b.f_dt_1_driver", "b.f_dt_2_plan", "b.f_dt_2_driver",
                "b.f_dt_3_plan", "b.f_dt_3_driver", "b.f_dt_4_plan", "b.f_dt_4_driver","b.f_dt_5_plan", "b.f_dt_5_driver",
                "b.f_memo", "a.f_vsl", "a.f_voy_num", "a.f_clients", "b.f_cnt_owner","b.f_srv_level","b.f_plan_act_dt",
                "b.f_unload_man", "b.f_unload_man_tel", "b.f_trace_memo", "b.f_status"
            };
            var indexId = colList.IndexOf("b.id");
            var indexPlanNumber = colList.IndexOf("b.f_plan_num");
            var indexCntNumber = colList.IndexOf("b.f_cnt_num");
            var indexArrivalDT = colList.IndexOf("a.f_arrival_dt");
            var indexAddress = colList.IndexOf("b.f_address");
            var indexCntType = colList.IndexOf("b.f_cnt_type");
            var indexPlanDT = colList.IndexOf("b.f_plan_dt");
            var indexDispatcher = colList.IndexOf("b.f_dispatcher");
            var indexTruckCompany = colList.IndexOf("b.f_truck_company");
            var indexTruckNumber = colList.IndexOf("b.f_truck_num");
            var indexDriver = colList.IndexOf("b.f_driver");
            var indexDriverTel = colList.IndexOf("b.f_driver_tel");
            var indexTruckDT = colList.IndexOf("b.f_truck_dt");
            var indexDT1Plan = colList.IndexOf("b.f_dt_1_plan");
            var indexDT1Driver = colList.IndexOf("b.f_dt_1_driver");
            var indexDT2Plan = colList.IndexOf("b.f_dt_2_plan");
            var indexDT2Driver = colList.IndexOf("b.f_dt_2_driver");
            var indexDT3Plan = colList.IndexOf("b.f_dt_3_plan");
            var indexDT3Driver = colList.IndexOf("b.f_dt_3_driver");
            var indexDT4Plan = colList.IndexOf("b.f_dt_4_plan");
            var indexDT4Driver = colList.IndexOf("b.f_dt_4_driver");
            var indexDT5Plan = colList.IndexOf("b.f_dt_5_plan");
            var indexDT5Driver = colList.IndexOf("b.f_dt_5_driver");
            var indexMemo = colList.IndexOf("b.f_memo");
            var indexVsl = colList.IndexOf("a.f_vsl");
            var indexVoyNumber = colList.IndexOf("a.f_voy_num");
            var indexClients = colList.IndexOf("a.f_clients");
            var indexCntOnwer = colList.IndexOf("b.f_cnt_owner");
            var indexUnloadMan = colList.IndexOf("b.f_unload_man");
            var indexUnloadManTel = colList.IndexOf("b.f_unload_man_tel");
            var indexTraceMemo = colList.IndexOf("b.f_trace_memo");
            var indexStatus = colList.IndexOf("b.f_status");

            var sb = new StringBuilder();
            sb.Append("select ").Append(colList.CombineWithComma());
            sb.Append(" from tms_in_consignation a, tms_in_consignation_detail b");
            sb.Append(" where (a.id = b.parent_id and b.f_status is not null) ");
            var p = CreateDbParameters();
            if (!string.IsNullOrEmpty(planNumber)) {
                sb.Append(" and (b.f_plan_num like @planNumber) ");
                p.AddWithValue("planNumber", "%" + planNumber + "%");
            }
            if (!string.IsNullOrEmpty(address)) {
                sb.Append(" and (b.f_address like @address) ");
                p.AddWithValue("address", "%" + address + "%");
            }
            if (!string.IsNullOrEmpty(cntNumber)) {
                sb.Append(" and (b.f_cnt_num like @cntNumber) ");
                p.AddWithValue("cntNumber", "%" + cntNumber + "%");
            }
            if (!string.IsNullOrEmpty(dispatcher)) {
                sb.Append(" and (b.f_dispatcher like @dispatcher) ");
                p.AddWithValue("dispatcher", "%" + dispatcher + "%");
            }
            if (!string.IsNullOrEmpty(truckCompany)) {
                sb.Append(" and (b.f_truck_company like @truckCompany) ");
                p.AddWithValue("truckCompany", "%" + truckCompany + "%");
            }
            if (!string.IsNullOrEmpty(plandt))
            {
                sb.Append(" and convert(date, b.f_plan_act_dt,120) like @pd");
                p.AddWithValue("pd", "%" + plandt + "%");
            }
            if (!string.IsNullOrEmpty(truckNumber)) {
                sb.Append(" and (b.f_truck_num like @truckNumber) ");
                p.AddWithValue("truckNumber", "%" + truckNumber + "%");
            }
            if (!string.IsNullOrEmpty(driver)) {
                sb.Append(" and (b.f_driver like @driver) ");
                p.AddWithValue("driver", "%" + driver + "%");
            }
            if (!string.IsNullOrEmpty(driverTel)) {
                sb.Append(" and (b.f_driver_tel like @driverTel) ");
                p.AddWithValue("driverTel", "%" + driverTel + "%");
            }
            if (!string.IsNullOrEmpty(consignationBillNumber)) {
                sb.Append(" and (a.f_bill_num like @consignationBillNumber) ");
                p.AddWithValue("consignationBillNumber", "%" + consignationBillNumber + "%");
            }
            if (!string.IsNullOrEmpty(planDT1) || !string.IsNullOrEmpty(planDT2)) {
                if (string.IsNullOrEmpty(planDT1)) {
                    planDT1 = planDT2;
                }
                if (string.IsNullOrEmpty(planDT2)) {
                    planDT2 = planDT1;
                }
                sb.Append(" and (convert(date, b.f_plan_dt) between @planDT1 and @planDT2)");
                p.AddWithValue("planDT1", planDT1);
                p.AddWithValue("planDT2", planDT2);
            }
            if (!string.IsNullOrEmpty(status))
            {
                sb.Append(" and (b.f_status = @status) ");
                p.AddWithValue("status", status);
            }
            sb.Append(" order by b.f_plan_flag desc, b.f_plan_num desc ");

            var sql = sb.ToString();
            var result = new List<InPlan>();
            Query(sql, p, reader => {
                var ip = new InPlan {
                    Id = reader.GetString(indexId),
                    PlanNumber = reader.GetString(indexPlanNumber),
                    CntNumber = reader.GetString(indexCntNumber),
                    DeliverDT = reader.GetDateTime(indexArrivalDT).ToYmdHm(),
                    Address = reader.GetString(indexAddress),
                    CntType = reader.GetString(indexCntType),
                    PlanDT = reader.GetDateTime(indexPlanDT).ToYmdHm(),
                    Dispather = reader.GetString(indexDispatcher),
                    TruckCompany = reader.GetString(indexTruckCompany),
                    TruckNumber = reader.GetString(indexTruckNumber),
                    Driver = reader.GetString(indexDriver),
                    DriverTel = reader.GetString(indexDriverTel),
                    TruckDT = reader.GetDateTime(indexTruckDT).ToYmdHm(),
                    DT1Plan = reader.GetDateTime(indexDT1Plan).ToYmdHm(),
                    DT1Driver = reader.GetDateTime(indexDT1Driver).ToYmdHm(),
                    DT2Plan = reader.GetDateTime(indexDT2Plan).ToYmdHm(),
                    DT2Driver = reader.GetDateTime(indexDT2Driver).ToYmdHm(),
                    DT3Plan = reader.GetDateTime(indexDT3Plan).ToYmdHm(),
                    DT3Driver = reader.GetDateTime(indexDT3Driver).ToYmdHm(),
                    DT4Plan = reader.GetDateTime(indexDT4Plan).ToYmdHm(),
                    DT4Driver = reader.GetDateTime(indexDT4Driver).ToYmdHm(),
                    DT5Plan = reader.GetDateTime(indexDT5Plan).ToYmdHm(),
                    DT5Driver = reader.GetDateTime(indexDT5Driver).ToYmdHm(),
                    SrvLevel = reader.GetString(reader.GetOrdinal("f_srv_level")),
                    Memo = reader.GetString(indexMemo)
                };
                if (reader.IsDBNull(indexPlanDT)) {
                    ip.PlanDT = null;
                }
                if (reader.IsDBNull(indexTruckDT)) {
                    ip.TruckDT = null;
                }
                if (reader.IsDBNull(indexDT1Plan)) {
                    ip.DT1Plan = null;
                }
                if (reader.IsDBNull(indexDT1Driver)) {
                    ip.DT1Driver = null;
                }
                if (reader.IsDBNull(indexDT2Plan)) {
                    ip.DT2Plan = null;
                }
                if (reader.IsDBNull(indexDT2Driver)) {
                    ip.DT2Driver = null;
                }
                if (reader.IsDBNull(indexDT3Plan)) {
                    ip.DT3Plan = null;
                }
                if (reader.IsDBNull(indexDT3Driver)) {
                    ip.DT3Driver = null;
                }
                if (reader.IsDBNull(indexDT4Plan)) {
                    ip.DT4Plan = null;
                }
                if (reader.IsDBNull(indexDT4Driver)) {
                    ip.DT4Driver = null;
                }
                if (reader.IsDBNull(indexDT5Plan))
                {
                    ip.DT5Plan = null;
                }
                if (reader.IsDBNull(indexDT5Driver))
                {
                    ip.DT5Driver = null;
                }

                ip.Vsl = reader.GetString(indexVsl);
                if (!string.IsNullOrEmpty(reader.GetString(indexVoyNumber))) {
                    ip.Vsl += "/" + reader.GetString(indexVoyNumber);
                }

                ip.Clients = reader.GetString(indexClients);
                ip.CntOwner = reader.GetString(indexCntOnwer);
                ip.UnloadMan = reader.GetString(indexUnloadMan);
                ip.UnloadManTel = reader.GetString(indexUnloadManTel);
                ip.TraceMemo = reader.GetString(indexTraceMemo);
                ip.Status = reader.GetString(indexStatus);

                result.Add(ip);

            });
            return result;
        }

        public List<InPlan> GetInPlanListData(NameValueCollection formData, bool allData = false) {
            var result = GetInPlanListWithFlag(formData);

            if (allData) {
                return result;
            } else {
                return result.Take(500).ToList();
            }
        }

        public string GetAutoNumber() {
            var mid = string.Format("{0:yyMMdd}", DateTime.Now);
            return new BillNumberService().GetAutoNumber("进口计划", "J", mid);
        }

        public Result MakePlan(NameValueCollection formData) {
            var idListJSON = formData["idList"];
            var dispatcher = formData["dispatcher"];
            var idList = JsonConvert.DeserializeObject<List<string>>(idListJSON);

            WithTransaction(() => {
                foreach (var id in idList) {
                    var sql = @"update tms_in_consignation_detail
                                set f_dispatcher = @dispatcher, f_plan_dt = getdate(), f_plan_num = @planNumber,
                                    f_plan_flag = 0, f_status = '待派车'
                                where id = @id and f_plan_dt is null
                            ";
                    var p = CreateDbParameters();
                    var planNumber = GetAutoNumber();

                    p.AddWithValue("id", id);
                    p.AddWithValue("dispatcher", dispatcher);
                    p.AddWithValue("planNumber", planNumber);
                    Execute(sql, p);

                    new LogService().Log("下达进口计划给调度:" + dispatcher + ",计划号: " + planNumber);
                }
            });

            return new Result { Success = true };
        }

        public Result ChangeDispatcher(NameValueCollection formData) {
            var idListJSON = formData["idList"];
            var dispatcher = formData["dispatcher"];
            var idList = JsonConvert.DeserializeObject<List<string>>(idListJSON);

            WithTransaction(() => {
                foreach (var id in idList) {
                    var sql = @"update tms_in_consignation_detail
                                set f_dispatcher = @dispatcher
                                where id = @id and f_plan_dt is not null
                            ";
                    var p = CreateDbParameters();
                    var planNumber = GetAutoNumber();

                    p.AddWithValue("id", id);
                    p.AddWithValue("dispatcher", dispatcher);
                    Execute(sql, p);

                    new LogService().Log("更改进口计划的调度， 新调度为:" + dispatcher + ",计划号: " + planNumber);
                }
            });

            return new Result { Success = true };
        }

        private string CombintDateTime(string d, string t) {
            var dt = d + " " + t;
            DateTime temp;
            return DateTime.TryParse(dt, out temp) ? dt : null;
        }

        /// <summary>
        /// 派车
        /// </summary>
        /// <param name="formData"></param>
        /// <returns></returns>
        public Result SendTruck(NameValueCollection formData) {
            var id = formData["id"];
            var truckCompany = formData["truckCompany"];
            var truckNumber = formData["truckNumber"];
            var driver = formData["driver"];
            var driverTel = formData["driverTel"];

            var tripNumber = formData["tripNumber"];

            var dt1 = CombintDateTime(formData["dt1Date"], formData["dt1Time"]);
            var dt2 = CombintDateTime(formData["dt2Date"], formData["dt2Time"]);
            var dt3 = CombintDateTime(formData["dt3Date"], formData["dt3Time"]);
            var dt4 = CombintDateTime(formData["dt4Date"], formData["dt4Time"]);
            var dt5 = CombintDateTime(formData["dt5Date"], formData["dt5Time"]);
            WithTransaction(() => {
                var sql = @"update tms_in_consignation_detail
                            set f_truck_company = @truckCompany, f_truck_num = @truckNumber,
                                f_driver = @driver, f_driver_tel = @driverTel, f_truck_dt = isnull(f_truck_dt, getdate()),
                                f_trip_number = @tripNumber, f_status = '已派车',
                                f_dt_1_plan = @dt1, f_dt_2_plan = @dt2, f_dt_3_plan = @dt3, f_dt_4_plan = @dt4, f_dt_5_plan = @dt5
                            where id = @id
                            ";
                var p = CreateDbParameters();
                p.AddWithValue("id", id);
                p.AddWithValue("truckCompany", truckCompany);
                p.AddWithValue("truckNumber", truckNumber);
                p.AddWithValue("driver", driver);
                p.AddWithValue("driverTel", driverTel);
                p.AddWithValue("dt1", dt1);
                p.AddWithValue("dt2", dt2);
                p.AddWithValue("dt3", dt3);
                p.AddWithValue("dt4", dt4);
                p.AddWithValue("dt5", dt5);
                p.AddWithValue("tripNumber", tripNumber);
                Execute(sql, p);

                new LogService().Log("派车，拖车公司：" + truckCompany + ",车号："
                    + truckNumber + ",司机：" + driver + ", 计划单Id = " + id);
            });

            var InConsignationDetil = new InConsignationService().GetInConsignationDetail(id);

            if (InConsignationDetil.isweixin == IsWXStatus.release) {
                var upstr = "update tms_weixin_resverimport set i_isselect=2 where v_orderID=@OrderId";

                var  pp = CreateDbParameters();

                pp.AddWithValue("OrderId", id);

                Execute(upstr,pp);
            }

            return new Result { Success = true };
        }

        public InPlan GetSendTruckInfo(string id) {
            var result = new InPlan {
                Id = id
            };
            var colList = new List<string> { 
                "f_truck_company", "f_truck_num", "f_driver", "f_driver_tel",
                "f_dt_1_plan", "f_dt_2_plan", "f_dt_3_plan", "f_dt_4_plan","f_dt_5_plan",
                "f_trip_number"
            };
            var indexTruckCompany = colList.IndexOf("f_truck_company");
            var indexTruckNumber = colList.IndexOf("f_truck_num");
            var indexDriver = colList.IndexOf("f_driver");
            var indexDriverTel = colList.IndexOf("f_driver_tel");
            var indexDT1Plan = colList.IndexOf("f_dt_1_plan");
            var indexDT2Plan = colList.IndexOf("f_dt_2_plan");
            var indexDT3Plan = colList.IndexOf("f_dt_3_plan");
            var indexDT4Plan = colList.IndexOf("f_dt_4_plan");
            var indexDT5Plan = colList.IndexOf("f_dt_5_plan");
            var indexTripNumber = colList.IndexOf("f_trip_number");

            var sql = "select " + colList.CombineWithComma()
                + " from tms_in_consignation_detail where id = @id";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            Query(sql, p, reader => {
                result.TruckCompany = reader.GetString(indexTruckCompany);
                result.TruckNumber = reader.GetString(indexTruckNumber);
                result.Driver = reader.GetString(indexDriver);
                result.DriverTel = reader.GetString(indexDriverTel);
                if (!reader.IsDBNull(indexDT1Plan)) {
                    var dt = reader.GetDateTime(indexDT1Plan);
                    result.DT1PlanDate = dt.ToYmd();
                    result.DT1PlanTime = dt.ToHm();
                }

                if (!reader.IsDBNull(indexDT2Plan)) {
                    var dt = reader.GetDateTime(indexDT2Plan);
                    result.DT2PlanDate = dt.ToYmd();
                    result.DT2PlanTime = dt.ToHm();
                }

                if (!reader.IsDBNull(indexDT3Plan)) {
                    var dt = reader.GetDateTime(indexDT3Plan);
                    result.DT3PlanDate = dt.ToYmd();
                    result.DT3PlanTime = dt.ToHm();
                }

                if (!reader.IsDBNull(indexDT4Plan)) {
                    var dt = reader.GetDateTime(indexDT4Plan);
                    result.DT4PlanDate = dt.ToYmd();
                    result.DT4PlanTime = dt.ToHm();
                }
                if (!reader.IsDBNull(indexDT5Plan))
                {
                    var dt = reader.GetDateTime(indexDT5Plan);
                    result.DT5PlanDate = dt.ToYmd();
                    result.DT5PlanTime = dt.ToHm();
                }
                result.TripNumber = reader.GetString(indexTripNumber);
            });

            return result;
        }

        public InPlan GetTraceInfo(string id) {
            var result = new InPlan {
                Id = id
            };

            var colList = new List<string> { 
                "f_truck_company", "f_truck_num", "f_driver", "f_driver_tel",
                "f_dt_1_driver", "f_dt_2_driver", "f_dt_3_driver", "f_dt_4_driver",
                "f_trace_memo"
            };
            var indexTruckCompany = colList.IndexOf("f_truck_company");
            var indexTruckNumber = colList.IndexOf("f_truck_num");
            var indexDriver = colList.IndexOf("f_driver");
            var indexDriverTel = colList.IndexOf("f_driver_tel");
            var indexDT1Driver = colList.IndexOf("f_dt_1_driver");
            var indexDT2Driver = colList.IndexOf("f_dt_2_driver");
            var indexDT3Driver = colList.IndexOf("f_dt_3_driver");
            var indexDT4Driver = colList.IndexOf("f_dt_4_driver");
            var indexTraceMemo = colList.IndexOf("f_trace_memo");

            var sql = "select " + colList.CombineWithComma()
                + " from tms_in_consignation_detail where id = @id";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            Query(sql, p, reader => {
                result.TruckCompany = reader.GetString(indexTruckCompany);
                result.TruckNumber = reader.GetString(indexTruckNumber);
                result.Driver = reader.GetString(indexDriver);
                result.DriverTel = reader.GetString(indexDriverTel);
                if (!reader.IsDBNull(indexDT1Driver)) {
                    var dt = reader.GetDateTime(indexDT1Driver);
                    result.DT1DriverDate = dt.ToYmd();
                    result.DT1DriverTime = dt.ToHm();
                }

                if (!reader.IsDBNull(indexDT2Driver)) {
                    var dt = reader.GetDateTime(indexDT2Driver);
                    result.DT2DriverDate = dt.ToYmd();
                    result.DT2DriverTime = dt.ToHm();
                }

                if (!reader.IsDBNull(indexDT3Driver)) {
                    var dt = reader.GetDateTime(indexDT3Driver);
                    result.DT3DriverDate = dt.ToYmd();
                    result.DT3DriverTime = dt.ToHm();
                }

                if (!reader.IsDBNull(indexDT4Driver)) {
                    var dt = reader.GetDateTime(indexDT4Driver);
                    result.DT4DriverDate = dt.ToYmd();
                    result.DT4DriverTime = dt.ToHm();
                }

                result.TraceMemo = reader.GetString(indexTraceMemo);
            });

            return result;
        }

        public Result Trace(NameValueCollection formData) {
            var id = formData["id"];

            var dt1 = CombintDateTime(formData["dt1Date"], formData["dt1Time"]);
            var dt2 = CombintDateTime(formData["dt2Date"], formData["dt2Time"]);
            var dt3 = CombintDateTime(formData["dt3Date"], formData["dt3Time"]);
            var dt4 = CombintDateTime(formData["dt4Date"], formData["dt4Time"]);
            var dt5 = CombintDateTime(formData["dt5Date"], formData["dt5Time"]);
            var traceMemo = formData["traceMemo"];

            WithTransaction(() => {
                var sql = @"update tms_in_consignation_detail
                            set f_dt_1_driver = @dt1, f_dt_2_driver = @dt2, f_dt_3_driver = @dt3, f_dt_4_driver = @dt4,f_dt_5_driver = @dt5,
                                f_trace_memo = @traceMemo
                            where id = @id
                            ";
                var p = CreateDbParameters();
                p.AddWithValue("id", id);
                p.AddWithValue("dt1", dt1);
                p.AddWithValue("dt2", dt2);
                p.AddWithValue("dt3", dt3);
                p.AddWithValue("dt4", dt4);
                p.AddWithValue("dt5", dt5);
                p.AddWithValue("traceMemo", traceMemo);
                Execute(sql, p);
            });

            return new Result { Success = true };
        }

        public string Excel(NameValueCollection formData, string filePath) {
            var data = this.GetInPlanListData(formData, true);
            var path = filePath + "/download";
            if (!Directory.Exists(path)) {
                Directory.CreateDirectory(path);
            }

            var fileName = path + "/" + IdGeneratorService.NewId() + ".xls";

            var workbook = new HSSFWorkbook();

            var sheet = workbook.CreateSheet("进口计划");
            sheet.DisplayGridlines = false;
            sheet.SetColumnWidth(0, 12 * 256); //12个字符宽
            sheet.SetColumnWidth(1, 20 * 256);
            sheet.SetColumnWidth(2, 20 * 256);
            sheet.SetColumnWidth(3, 20 * 256);
            sheet.SetColumnWidth(4, 10 * 256);
            sheet.SetColumnWidth(5, 20 * 256);
            sheet.SetColumnWidth(6, 10 * 256);
            sheet.SetColumnWidth(7, 16 * 256);
            sheet.SetColumnWidth(8, 16 * 256);
            sheet.SetColumnWidth(9, 16 * 256);
            sheet.SetColumnWidth(10, 6 * 256);
            sheet.SetColumnWidth(11, 20 * 256);
            sheet.SetColumnWidth(12, 12 * 256);
            sheet.SetColumnWidth(13, 8 * 256);
            sheet.SetColumnWidth(14, 12 * 256);
            sheet.SetColumnWidth(15, 16 * 256);
            sheet.SetColumnWidth(16, 12 * 256);
            sheet.SetColumnWidth(17, 20 * 256);
            sheet.SetColumnWidth(18, 16 * 256);
            sheet.SetColumnWidth(19, 16 * 256);
            sheet.SetColumnWidth(20, 16 * 256);
            sheet.SetColumnWidth(21, 16 * 256);
            sheet.SetColumnWidth(22, 16 * 256);
            sheet.SetColumnWidth(23, 16 * 256);
            sheet.SetColumnWidth(24, 16 * 256);
            sheet.SetColumnWidth(25, 16 * 256);
            sheet.SetColumnWidth(26, 16 * 256);
            sheet.SetColumnWidth(27, 16 * 256);
            sheet.SetColumnWidth(28, 16 * 256); // 动态备注

            // 创建表头
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 26));
            var rowHead1 = sheet.CreateRow(0);
            var cellHead = rowHead1.CreateCell(0);
            cellHead.SetCellValue("进口计划");
            var s = workbook.CreateCellStyle();
            s.Alignment = HorizontalAlignment.Center;
            var f = workbook.CreateFont();
            f.FontHeight = 20 * 20;
            s.SetFont(f);
            cellHead.CellStyle = s;

            var rowHead2 = sheet.CreateRow(1);
            // 创建列头
            var heads1 = new string[] {"进口计划号", "船名/航次", "委托方", "门点", "箱属", 
                "箱号/封号", "箱型", "送货时限", "备注", "计划下达时间", "调度",
                "拖车公司", "车号", "司机", "司机电话", "派车时间", "卸货联系人", "卸货人电话",
                "提箱","提箱", "出港","出港","到卸货地","到卸货地","卸货开始", "卸货开始","卸货完成", "卸货完成", "动态备注"
            };
            for (int i = 0; i < heads1.Length; i++) {
                var cell = rowHead2.CreateCell(i);
                var style = workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;

                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.BorderTop = BorderStyle.Thin;
                style.FillPattern = FillPattern.NoFill;
                style.FillBackgroundColor = HSSFColor.Grey25Percent.Index;

                var font = workbook.CreateFont();
                font.FontHeight = 12 * 20;
                style.SetFont(font);
                cell.CellStyle = style;
                cell.SetCellValue(heads1[i]);
            }
            var heads = new string[] {"进口计划号", "船名/航次", "委托方", "门点", "箱属",
                "箱号/封号", "箱型", "送货时限", "备注", "计划下达时间", "调度",
                "拖车公司", "车号", "司机", "司机电话", "派车时间","卸货联系人", "卸货人电话",
                "调度计划时间","司机报点","调度计划时间","司机报点","调度计划时间","司机报点","调度计划时间","司机报点","调度计划时间","司机报点",
                "动态备注"
            };
            var rowColHead = sheet.CreateRow(2);
            for (int i = 0; i < heads.Length; i++) {
                var cell = rowColHead.CreateCell(i);
                var style = workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;
                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.BorderTop = BorderStyle.Thin;
                style.FillPattern = FillPattern.NoFill;
                style.FillBackgroundColor = HSSFColor.Grey25Percent.Index;

                var font = workbook.CreateFont();
                font.FontHeight = 12 * 20;
                style.SetFont(font);

                cell.CellStyle = style;
                cell.SetCellValue(heads[i]);
            }


            for (int i = 0; i < 18; i++) {
                sheet.AddMergedRegion(new CellRangeAddress(1, 2, i, i));
            }
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 18, 19));
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 20, 21));
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 22, 23));
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 24, 25));
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 26, 27));
            sheet.AddMergedRegion(new CellRangeAddress(1, 2, 28, 28)); // 动态备注
            // 表格明细数据
            var startRow = 3;
            for (int i = startRow; i < data.Count + startRow; i++) {
                var dataIndex = i - startRow;
                var plan = data[dataIndex];

                var row = sheet.CreateRow(i);
                for (int colIndex = 0; colIndex < heads.Length; colIndex++) {
                    var cell = row.CreateCell(colIndex);

                    var style = workbook.CreateCellStyle();
                    style.BorderBottom = BorderStyle.Thin;
                    style.BorderLeft = BorderStyle.Thin;
                    style.BorderRight = BorderStyle.Thin;
                    style.BorderTop = BorderStyle.Thin;

                    cell.CellStyle = style;
                    switch (colIndex) {
                        case 0:
                            cell.SetCellValue(plan.PlanNumber);
                            break;
                        case 1:
                            cell.SetCellValue(plan.Vsl);
                            break;
                        case 2:
                            cell.SetCellValue(plan.Clients);
                            break;
                        case 3:
                            cell.SetCellValue(plan.Address);
                            break;
                        case 4:
                            cell.SetCellValue(plan.CntOwner);
                            break;
                        case 5:
                            cell.SetCellValue(plan.CntNumber);
                            break;
                        case 6:
                            cell.SetCellValue(plan.CntType);
                            break;
                        case 7:
                            cell.SetCellValue(plan.DeliverDT);
                            break;
                        case 8:
                            cell.SetCellValue(plan.Memo);
                            break;
                        case 9:
                            cell.SetCellValue(plan.PlanDT);
                            break;
                        case 10:
                            cell.SetCellValue(plan.Dispather);
                            break;
                        case 11:
                            cell.SetCellValue(plan.TruckCompany);
                            break;
                        case 12:
                            cell.SetCellValue(plan.TruckNumber);
                            break;
                        case 13:
                            cell.SetCellValue(plan.Driver);
                            break;
                        case 14:
                            cell.SetCellValue(plan.DriverTel);
                            break;
                        case 15:
                            cell.SetCellValue(plan.TruckDT);
                            break;
                        case 16:
                            cell.SetCellValue(plan.UnloadMan);
                            break;
                        case 17:
                            cell.SetCellValue(plan.UnloadManTel);
                            break;
                        case 18:
                            cell.SetCellValue(plan.DT1Plan);
                            break;
                        case 19:
                            cell.SetCellValue(plan.DT1Driver);
                            break;
                        case 20:
                            cell.SetCellValue(plan.DT2Plan);
                            break;
                        case 21:
                            cell.SetCellValue(plan.DT2Driver);
                            break;
                        case 22:
                            cell.SetCellValue(plan.DT3Plan);
                            break;
                        case 23:
                            cell.SetCellValue(plan.DT3Driver);
                            break;
                        case 24:
                            cell.SetCellValue(plan.DT5Plan);
                            break;
                        case 25:
                            cell.SetCellValue(plan.DT5Driver);
                            break;
                        case 26:
                            cell.SetCellValue(plan.DT4Plan);
                            break;
                        case 27:
                            cell.SetCellValue(plan.DT4Driver);
                            break;
                        case 28:
                            cell.SetCellValue(plan.TraceMemo);
                            break;
                        default:
                            break;
                    }
                }
            }

            var file = new FileStream(fileName, FileMode.Create);
            workbook.Write(file);
            file.Close();

            return fileName;
        }

        public List<InPlan> GetMakePlanListData() {
            var colList = new List<string> { 
                "b.id", "b.f_plan_num", "b.f_cnt_num", "a.f_arrival_dt", "b.f_address",
                "b.f_cnt_type", "b.f_plan_dt", "b.f_dispatcher",
                "b.f_memo", "a.f_vsl", "a.f_voy_num", "a.f_clients", "b.f_cnt_owner", 
                "b.f_unload_man", "b.f_unload_man_tel", "b.f_status"
            };
            var indexId = colList.IndexOf("b.id");
            var indexPlanNumber = colList.IndexOf("b.f_plan_num");
            var indexCntNumber = colList.IndexOf("b.f_cnt_num");
            var indexArrivalDT = colList.IndexOf("a.f_arrival_dt");
            var indexAddress = colList.IndexOf("b.f_address");
            var indexCntType = colList.IndexOf("b.f_cnt_type");
            var indexPlanDT = colList.IndexOf("b.f_plan_dt");
            var indexDispatcher = colList.IndexOf("b.f_dispatcher");
            var indexMemo = colList.IndexOf("b.f_memo");
            var indexVsl = colList.IndexOf("a.f_vsl");
            var indexVoyNumber = colList.IndexOf("a.f_voy_num");
            var indexClients = colList.IndexOf("a.f_clients");
            var indexCntOwner = colList.IndexOf("b.f_cnt_owner");
            var indexUnloadMan = colList.IndexOf("b.f_unload_man");
            var indexUnloadManTel = colList.IndexOf("b.f_unload_man_tel");
            var indexStatus = colList.IndexOf("b.f_status");

            // 查询出没有下达计划的和当日下达的计划
            var sql = "select " + colList.CombineWithComma()
                + @" from tms_in_consignation a, tms_in_consignation_detail b
                where (a.id = b.parent_id) and (b.f_status = '待下达计划' or b.f_status = '待派车') 
                    and (b.f_plan_act_dt = convert(date,getdate()))
                order by b.f_plan_flag desc, b.f_plan_num desc
            ";
            var result = new List<InPlan>();
            Query(sql, reader => {
                var ip = new InPlan {
                    Id = reader.GetString(indexId),
                    PlanNumber = reader.GetString(indexPlanNumber),
                    CntNumber = reader.GetString(indexCntNumber),
                    DeliverDT = reader.GetDateTime(indexArrivalDT).ToYmdHm(),
                    Address = reader.GetString(indexAddress),
                    CntType = reader.GetString(indexCntType),
                    PlanDT = reader.GetDateTime(indexPlanDT).ToYmdHm(),
                    Dispather = reader.GetString(indexDispatcher),
                    Memo = reader.GetString(indexMemo)
                };
                if (reader.IsDBNull(indexPlanDT)) {
                    ip.PlanDT = null;
                }

                ip.Vsl = reader.GetString(indexVsl);
                if (!string.IsNullOrEmpty(reader.GetString(indexVoyNumber))) {
                    ip.Vsl += "/" + reader.GetString(indexVoyNumber);
                }

                ip.Clients = reader.GetString(indexClients);
                ip.CntOwner = reader.GetString(indexCntOwner);
                ip.UnloadMan = reader.GetString(indexUnloadMan);
                ip.UnloadManTel = reader.GetString(indexUnloadManTel);
                ip.Status = reader.GetString(indexStatus);

                result.Add(ip);

            });
            return result;
        }

        public List<InPlan> GetSendTruckListData(NameValueCollection formData) {
            var dispatcher = formData["dispatcher"];
            var truckCompany = formData["truckCompany"];
            var truckNumber = formData["truckNumber"];
            var driver = formData["driver"];
            var driverTel = formData["driverTel"];

            var colList = new List<string> { 
                "b.id", "b.f_plan_num", "b.f_cnt_num", "a.f_arrival_dt", "b.f_address",
                "b.f_cnt_type", "b.f_plan_dt", "b.f_dispatcher",
                "b.f_truck_company", "b.f_truck_num", "b.f_driver", "b.f_driver_tel",
                "b.f_truck_dt", "b.f_dt_1_plan", "b.f_dt_2_plan",
                "b.f_dt_3_plan", "b.f_dt_4_plan","b.f_dt_5_plan",
                "b.f_memo", "a.f_vsl", "a.f_voy_num", "a.f_clients", 
                "b.f_cnt_owner", "b.f_unload_man", "b.f_unload_man_tel", "b.f_trip_number", "b.f_status"
            };
            var indexId = colList.IndexOf("b.id");
            var indexPlanNumber = colList.IndexOf("b.f_plan_num");
            var indexCntNumber = colList.IndexOf("b.f_cnt_num");
            var indexArrivalDT = colList.IndexOf("a.f_arrival_dt");
            var indexAddress = colList.IndexOf("b.f_address");
            var indexCntType = colList.IndexOf("b.f_cnt_type");
            var indexPlanDT = colList.IndexOf("b.f_plan_dt");
            var indexDispatcher = colList.IndexOf("b.f_dispatcher");
            var indexTruckCompany = colList.IndexOf("b.f_truck_company");
            var indexTruckNumber = colList.IndexOf("b.f_truck_num");
            var indexDriver = colList.IndexOf("b.f_driver");
            var indexDriverTel = colList.IndexOf("b.f_driver_tel");
            var indexTruckDT = colList.IndexOf("b.f_truck_dt");
            var indexDT1Plan = colList.IndexOf("b.f_dt_1_plan");
            var indexDT2Plan = colList.IndexOf("b.f_dt_2_plan");
            var indexDT3Plan = colList.IndexOf("b.f_dt_3_plan");
            var indexDT4Plan = colList.IndexOf("b.f_dt_4_plan");
            var indexDT5Plan = colList.IndexOf("b.f_dt_5_plan");
            var indexMemo = colList.IndexOf("b.f_memo");
            var indexVsl = colList.IndexOf("a.f_vsl");
            var indexVoyNumber = colList.IndexOf("a.f_voy_num");
            var indexClients = colList.IndexOf("a.f_clients");
            var indexCntOwner = colList.IndexOf("b.f_cnt_owner");
            var indexUnloadMan = colList.IndexOf("b.f_unload_man");
            var indexUnloadManTel = colList.IndexOf("b.f_unload_man_tel");
            var indexTripNumber = colList.IndexOf("b.f_trip_number");
            var indexStatus = colList.IndexOf("b.f_status");

            var sb = new StringBuilder();
            sb.Append("select ").Append(colList.CombineWithComma());
            sb.Append(" from tms_in_consignation a, tms_in_consignation_detail b");
            sb.Append(" where (a.id = b.parent_id) and ((b.f_status = '待派车') or(b.f_status = '已派车')) ");
            sb.Append("     and (b.f_plan_act_dt = convert(date, getdate())) ");
            var p = CreateDbParameters();
            if (!string.IsNullOrEmpty(dispatcher)) {
                sb.Append(" and (b.f_dispatcher like @dispatcher) ");
                p.AddWithValue("dispatcher", "%" + dispatcher + "%");
            }
            if (!string.IsNullOrEmpty(truckCompany)) {
                sb.Append(" and (b.f_truck_company like @truckCompany) ");
                p.AddWithValue("truckCompany", "%" + truckCompany + "%");
            }
            if (!string.IsNullOrEmpty(truckNumber)) {
                sb.Append(" and (b.f_truck_num like @truckNumber) ");
                p.AddWithValue("truckNumber", "%" + truckNumber + "%");
            }
            if (!string.IsNullOrEmpty(driver)) {
                sb.Append(" and (b.f_driver like @driver) ");
                p.AddWithValue("driver", "%" + driver + "%");
            }
            if (!string.IsNullOrEmpty(driverTel)) {
                sb.Append(" and (b.f_driver_tel like @driverTel) ");
                p.AddWithValue("driverTel", "%" + driverTel + "%");
            }
            sb.Append(" order by b.f_trip_number desc, b.f_truck_dt desc ");

            var sql = sb.ToString();
            var result = new List<InPlan>();
            Query(sql, p, reader => {
                var ip = new InPlan {
                    Id = reader.GetString(indexId),
                    PlanNumber = reader.GetString(indexPlanNumber),
                    CntNumber = reader.GetString(indexCntNumber),
                    DeliverDT = reader.GetDateTime(indexArrivalDT).ToYmdHm(),
                    Address = reader.GetString(indexAddress),
                    CntType = reader.GetString(indexCntType),
                    PlanDT = reader.GetDateTime(indexPlanDT).ToYmdHm(),
                    Dispather = reader.GetString(indexDispatcher),
                    TruckCompany = reader.GetString(indexTruckCompany),
                    TruckNumber = reader.GetString(indexTruckNumber),
                    Driver = reader.GetString(indexDriver),
                    DriverTel = reader.GetString(indexDriverTel),
                    TruckDT = reader.GetDateTime(indexTruckDT).ToYmdHm(),
                    DT1Plan = reader.GetDateTime(indexDT1Plan).ToYmdHm(),
                    DT2Plan = reader.GetDateTime(indexDT2Plan).ToYmdHm(),
                    DT3Plan = reader.GetDateTime(indexDT3Plan).ToYmdHm(),
                    DT4Plan = reader.GetDateTime(indexDT4Plan).ToYmdHm(),
                    DT5Plan = reader.GetDateTime(indexDT5Plan).ToYmdHm(),
                    Memo = reader.GetString(indexMemo)
                };
                if (reader.IsDBNull(indexPlanDT)) {
                    ip.PlanDT = null;
                }
                if (reader.IsDBNull(indexTruckDT)) {
                    ip.TruckDT = null;
                }
                if (reader.IsDBNull(indexDT1Plan)) {
                    ip.DT1Plan = null;
                }
                if (reader.IsDBNull(indexDT2Plan)) {
                    ip.DT2Plan = null;
                }
                if (reader.IsDBNull(indexDT3Plan)) {
                    ip.DT3Plan = null;
                }
                if (reader.IsDBNull(indexDT4Plan)) {
                    ip.DT4Plan = null;
                }
                if (reader.IsDBNull(indexDT5Plan))
                {
                    ip.DT5Plan = null;
                }

                ip.Vsl = reader.GetString(indexVsl);
                if (!string.IsNullOrEmpty(reader.GetString(indexVoyNumber))) {
                    ip.Vsl += "/" + reader.GetString(indexVoyNumber);
                }

                ip.Clients = reader.GetString(indexClients);
                ip.CntOwner = reader.GetString(indexCntOwner);
                ip.UnloadMan = reader.GetString(indexUnloadMan);
                ip.UnloadManTel = reader.GetString(indexUnloadManTel);
                ip.TripNumber = reader.GetString(indexTripNumber);
                ip.Status = reader.GetString(indexStatus);

                result.Add(ip);

            });
            return result;

        }

        public List<InPlan> GetTraceListData() {
            var colList = new List<string> {
                "b.id", "b.f_plan_num", "b.f_cnt_num", "a.f_arrival_dt", "b.f_address",
                "b.f_cnt_type", "b.f_plan_dt", "b.f_dispatcher",
                "b.f_truck_company", "b.f_truck_num", "b.f_driver", "b.f_driver_tel",
                "b.f_truck_dt", "b.f_dt_1_plan", "b.f_dt_1_driver", "b.f_dt_2_plan", "b.f_dt_2_driver",
                "b.f_dt_3_plan", "b.f_dt_3_driver", "b.f_dt_4_plan", "b.f_dt_4_driver","b.f_dt_5_plan", "b.f_dt_5_driver",
                "b.f_memo", "a.f_vsl", "a.f_voy_num", "a.f_clients", "b.f_cnt_owner", 
                "b.f_unload_man", "b.f_unload_man_tel", "b.f_trace_memo", "b.f_status"
            };

            var indexId = colList.IndexOf("b.id");
            var indexPlanNumber = colList.IndexOf("b.f_plan_num");
            var indexCntNumber = colList.IndexOf("b.f_cnt_num");
            var indexArrivalDT = colList.IndexOf("a.f_arrival_dt");
            var indexAddress = colList.IndexOf("b.f_address");
            var indexCntType = colList.IndexOf("b.f_cnt_type");
            var indexPlanDT = colList.IndexOf("b.f_plan_dt");
            var indexDispatcher = colList.IndexOf("b.f_dispatcher");
            var indexTruckCompany = colList.IndexOf("b.f_truck_company");
            var indexTruckNumber = colList.IndexOf("b.f_truck_num");
            var indexDriver = colList.IndexOf("b.f_driver");
            var indexDriverTel = colList.IndexOf("b.f_driver_tel");
            var indexTruckDT = colList.IndexOf("b.f_truck_dt");
            var indexDT1Plan = colList.IndexOf("b.f_dt_1_plan");
            var indexDT1Driver = colList.IndexOf("b.f_dt_1_driver");
            var indexDT2Plan = colList.IndexOf("b.f_dt_2_plan");
            var indexDT2Driver = colList.IndexOf("b.f_dt_2_driver");
            var indexDT3Plan = colList.IndexOf("b.f_dt_3_plan");
            var indexDT3Driver = colList.IndexOf("b.f_dt_3_driver");
            var indexDT4Plan = colList.IndexOf("b.f_dt_4_plan");
            var indexDT4Driver = colList.IndexOf("b.f_dt_4_driver");
            var indexDT5Plan = colList.IndexOf("b.f_dt_5_plan");
            var indexDT5Driver = colList.IndexOf("b.f_dt_5_driver");
            var indexMemo = colList.IndexOf("b.f_memo");
            var indexVsl = colList.IndexOf("a.f_vsl");
            var indexVoyNumber = colList.IndexOf("a.f_voy_num");
            var indexClients = colList.IndexOf("a.f_clients");
            var indexCntOwner = colList.IndexOf("b.f_cnt_owner");
            var indexUnloadMan = colList.IndexOf("b.f_unload_man");
            var indexUnloadManTel = colList.IndexOf("b.f_unload_man_tel");
            var indexTraceMemo = colList.IndexOf("b.f_trace_memo");
            var indexStatus = colList.IndexOf("b.f_status");

            var sb = new StringBuilder();
            sb.Append("select ").Append(colList.CombineWithComma());
            sb.Append(" from tms_in_consignation a, tms_in_consignation_detail b");
            sb.Append(" where (a.id = b.parent_id and b.f_status = '已派车')");
            var p = CreateDbParameters();
            sb.Append(" order by b.f_plan_num desc ");

            var sql = sb.ToString();
            var result = new List<InPlan>();
            Query(sql, p, reader => {
                var ip = new InPlan {
                    Id = reader.GetString(indexId),
                    PlanNumber = reader.GetString(indexPlanNumber),
                    CntNumber = reader.GetString(indexCntNumber),
                    DeliverDT = reader.GetDateTime(indexArrivalDT).ToYmdHm(),
                    Address = reader.GetString(indexAddress),
                    CntType = reader.GetString(indexCntType),
                    PlanDT = reader.GetDateTime(indexPlanDT).ToYmdHm(),
                    Dispather = reader.GetString(indexDispatcher),
                    TruckCompany = reader.GetString(indexTruckCompany),
                    TruckNumber = reader.GetString(indexTruckNumber),
                    Driver = reader.GetString(indexDriver),
                    DriverTel = reader.GetString(indexDriverTel),
                    TruckDT = reader.GetDateTime(indexTruckDT).ToYmdHm(),
                    DT1Plan = reader.GetDateTime(indexDT1Plan).ToYmdHm(),
                    DT1Driver = reader.GetDateTime(indexDT1Driver).ToYmdHm(),
                    DT2Plan = reader.GetDateTime(indexDT2Plan).ToYmdHm(),
                    DT2Driver = reader.GetDateTime(indexDT2Driver).ToYmdHm(),
                    DT3Plan = reader.GetDateTime(indexDT3Plan).ToYmdHm(),
                    DT3Driver = reader.GetDateTime(indexDT3Driver).ToYmdHm(),
                    DT4Plan = reader.GetDateTime(indexDT4Plan).ToYmdHm(),
                    DT4Driver = reader.GetDateTime(indexDT4Driver).ToYmdHm(),
                    DT5Plan = reader.GetDateTime(indexDT5Plan).ToYmdHm(),
                    DT5Driver = reader.GetDateTime(indexDT5Driver).ToYmdHm(),
                    Memo = reader.GetString(indexMemo)
                };
                if (reader.IsDBNull(indexPlanDT)) {
                    ip.PlanDT = null;
                }
                if (reader.IsDBNull(indexTruckDT)) {
                    ip.TruckDT = null;
                }
                if (reader.IsDBNull(indexDT1Plan)) {
                    ip.DT1Plan = null;
                }
                if (reader.IsDBNull(indexDT1Driver)) {
                    ip.DT1Driver = null;
                }
                if (reader.IsDBNull(indexDT2Plan)) {
                    ip.DT2Plan = null;
                }
                if (reader.IsDBNull(indexDT2Driver)) {
                    ip.DT2Driver = null;
                }
                if (reader.IsDBNull(indexDT3Plan)) {
                    ip.DT3Plan = null;
                }
                if (reader.IsDBNull(indexDT3Driver)) {
                    ip.DT3Driver = null;
                }
                if (reader.IsDBNull(indexDT4Plan)) {
                    ip.DT4Plan = null;
                }
                if (reader.IsDBNull(indexDT4Driver)) {
                    ip.DT4Driver = null;
                }
                if (reader.IsDBNull(indexDT5Plan))
                {
                    ip.DT5Plan = null;
                }
                if (reader.IsDBNull(indexDT5Driver))
                {
                    ip.DT5Driver = null;
                }

                ip.Vsl = reader.GetString(indexVsl);
                if (!string.IsNullOrEmpty(reader.GetString(indexVoyNumber))) {
                    ip.Vsl += "/" + reader.GetString(indexVoyNumber);
                }

                ip.Clients = reader.GetString(indexClients);
                ip.CntOwner = reader.GetString(indexCntOwner);
                ip.UnloadMan = reader.GetString(indexUnloadMan);
                ip.UnloadManTel = reader.GetString(indexUnloadManTel);
                ip.TraceMemo = reader.GetString(indexTraceMemo);
                ip.Status = reader.GetString(indexStatus);

                result.Add(ip);

            });
            return result;

        }

        public Result CancelPlan(string id) {
            //检查是否可以取消计划
            var sql = "select f_status from tms_in_consignation_detail where id = @id";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            var status = string.Empty;
            Query(sql, p, reader => {
                status = reader.GetString(0);
            });

            if (status != "待派车") {
                return new Result { Success = false, Messgae = "当前计划状态为[" + status + "]，不能取消" };
            }

            WithTransaction(() => {
                sql = @"update tms_in_consignation_detail
                        set f_status = '待下达计划', f_plan_dt = null, f_plan_flag = 1,
                            f_dispatcher = null, f_plan_num = null
                        where id = @id
                        ";
                p = CreateDbParameters();
                p.AddWithValue("id", id);
                Execute(sql, p);
            });

            return new Result { Success = true };
        }

        public Result CancelTruck(string id) {
            // 检查能否取消派车
            var sql = @"select count(*) from tms_in_consignation_detail
                        where (id = @id) and (DATEDIFF(hour, getdate(), f_truck_dt) > 6)";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            var cnt = QueryCount(sql, p);
            if (cnt > 0) {
                return new Result { Success = false, Messgae = "派车时间已经超过六个小时，这时不能取消派车" };
            }

            WithTransaction(() => {
                sql = @"update tms_in_consignation_detail
                            set f_status = '待派车', f_truck_company = null, f_truck_num = null,
                                f_truck_dt = null, f_driver = null, f_driver_tel = null,
                                f_dt_1_plan = null, f_dt_1_driver = null,
                                f_dt_2_plan = null, f_dt_2_driver = null,
                                f_dt_3_plan = null, f_dt_3_driver = null,
                                f_dt_4_plan = null, f_dt_4_driver = null,
                                f_trace_memo = null, f_trip_number = null
                            where id = @id
                        ";
                p = CreateDbParameters();
                p.AddWithValue("id", id);
                Execute(sql, p);
            });

            return new Result { Success = true };
        }

        public Result Finish(string id) {
            // 检查能否标记完成
            var sql = @"select count(*) from tms_in_consignation_detail
                        where (id = @id) and (f_dt_4_driver is null)";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);

            var cnt = QueryCount(sql, p);
            if (cnt > 0) {
                return new Result { Success = false, Messgae = "因为完成时间没有录入，计划不能标记为运输完成" };
            }

            sql = @"select count(*) from tms_in_consignation_detail
                        where (id = @id) and (f_srv_level is null)";
            p = CreateDbParameters();
            p.AddWithValue("id", id);

            cnt = QueryCount(sql, p);
            //if (cnt > 0) {
            //    return new Result { Success = false, Messgae = "因为没有做服务评价，计划不能标记为运输完成" };
            //}

            WithTransaction(() => {
                sql = @"update tms_in_consignation_detail
                            set f_status = '运输完成'
                        where id = @id
                        ";
                p = CreateDbParameters();
                p.AddWithValue("id", id);
                Execute(sql, p);
            });

            return new Result { Success = true };
        }

        public InPlan GetTraceComments(string id) {
            var result = new InPlan {
                Id = id
            };

            var colList = new List<string> { 
                "f_truck_company", "f_truck_num", "f_driver", "f_driver_tel",
                "f_dt_1_driver", "f_dt_2_driver", "f_dt_3_driver", "f_dt_4_driver",
                "f_trace_memo", "f_srv_level", "f_srv_clients_memo"
            };
            var indexTruckCompany = colList.IndexOf("f_truck_company");
            var indexTruckNumber = colList.IndexOf("f_truck_num");
            var indexDriver = colList.IndexOf("f_driver");
            var indexDriverTel = colList.IndexOf("f_driver_tel");
            var indexDT1Driver = colList.IndexOf("f_dt_1_driver");
            var indexDT2Driver = colList.IndexOf("f_dt_2_driver");
            var indexDT3Driver = colList.IndexOf("f_dt_3_driver");
            var indexDT4Driver = colList.IndexOf("f_dt_4_driver");
            var indexTraceMemo = colList.IndexOf("f_trace_memo");
            var indexSrvLevel = colList.IndexOf("f_srv_level");
            var indexSrvClientsMemo = colList.IndexOf("f_srv_clients_memo");

            var sql = "select " + colList.CombineWithComma()
                + " from tms_in_consignation_detail where id = @id";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            Query(sql, p, reader => {
                result.TruckCompany = reader.GetString(indexTruckCompany);
                result.TruckNumber = reader.GetString(indexTruckNumber);
                result.Driver = reader.GetString(indexDriver);
                result.DriverTel = reader.GetString(indexDriverTel);
                if (!reader.IsDBNull(indexDT1Driver)) {
                    var dt = reader.GetDateTime(indexDT1Driver);
                    result.DT1DriverDate = dt.ToYmd();
                    result.DT1DriverTime = dt.ToHm();
                }

                if (!reader.IsDBNull(indexDT2Driver)) {
                    var dt = reader.GetDateTime(indexDT2Driver);
                    result.DT2DriverDate = dt.ToYmd();
                    result.DT2DriverTime = dt.ToHm();
                }

                if (!reader.IsDBNull(indexDT3Driver)) {
                    var dt = reader.GetDateTime(indexDT3Driver);
                    result.DT3DriverDate = dt.ToYmd();
                    result.DT3DriverTime = dt.ToHm();
                }

                if (!reader.IsDBNull(indexDT4Driver)) {
                    var dt = reader.GetDateTime(indexDT4Driver);
                    result.DT4DriverDate = dt.ToYmd();
                    result.DT4DriverTime = dt.ToHm();
                }

                result.TraceMemo = reader.GetString(indexTraceMemo);

                result.SrvLevel = reader.GetString(indexSrvLevel);
                result.SrvClientsMemo = reader.GetString(indexSrvClientsMemo);
            });

            return result;
        }

        public Result TraceComments(NameValueCollection formData) {
            var id = formData["id"];
            var srvLevel = formData["srvLevel"];
            var srvClientsMemo = formData["srvClientsMemo"];

            var sql = @"update tms_in_consignation_detail
                        set f_srv_level = @srvLevel, f_srv_clients_memo = @srvClientsMemo 
                        where id = @id";
            var p = CreateDbParameters();
            p.AddWithValue("id", id);
            p.AddWithValue("srvLevel", srvLevel);
            p.AddWithValue("srvClientsMemo", srvClientsMemo);

            Execute(sql, p);

            return new Result { Success = true };
        }
    }
}